package com.fzz.learnitservice.service.impl.task;

import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * 数据清理，现逻辑主要为去重相关单词表
 */
@Slf4j
@Component
public class WordCleanupTask {

    @Resource
    private DataSource dataSource;


    /**
     * 清理单词表（word_level, word_phrase, word_example）的数据，去重
     */
    public void cleanDuplicates() {
        log.info("🧹 [开始清理单词相关表重复数据] 时间：{}", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));

        // 用数组存储表名与对应清理SQL，保持顺序一致
        String[] tableNames = {"word_level", "word_phrase", "word_example"};
        String[] sqls = {
                "DELETE FROM word_level WHERE id NOT IN (SELECT min_id FROM (SELECT MIN(id) AS min_id FROM word_level GROUP BY word_id, level) tmp);",
                "DELETE FROM word_phrase WHERE id NOT IN (SELECT min_id FROM (SELECT MIN(id) AS min_id FROM word_phrase GROUP BY word_id, phrase, meaning) tmp);",
                "DELETE FROM word_example WHERE id NOT IN (SELECT min_id FROM (SELECT MIN(id) AS min_id FROM word_example GROUP BY word_id, sentence_en, sentence_cn) tmp);"
        };

        int ans = 0;
        try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
            // 遍历表名和SQL，关联日志输出
            for (String sql : sqls) {
                String tableName = tableNames[ans++];
                int deleted = stmt.executeUpdate(sql);
                log.info("✅ 表 [{}] 清理完成：删除重复数据 {} 条", tableName, deleted);
            }
            log.info("🎉 所有表重复数据清理完毕");
        } catch (Exception e) {
            log.error("❌ 清理重复数据失败", e);
        }
    }



    // 每天凌晨 3 点执行（可调整）
//    @Scheduled(cron = "0 0 3 * * ?")
    public void startClean() {
        this.cleanDuplicates();
    }
}